#!/usr/local/bin/perl -w

use App::Options (
    options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
    option => {
        dbclass  => { default => "App::Repository::MySQL", },
        dbdriver => { default => "mysql", },
        dbhost   => { default => "localhost", },
        dbname   => { default => "test", },
        dbuser   => { default => "", },
        dbpass   => { default => "", },
    },
);

use Test::More qw(no_plan);
use lib "../App-Context/lib";
use lib "../../App-Context/lib";
use lib "lib";
use lib "../lib";
use lib ".";
use lib "t";

use App;
use App::Repository;
use RepositoryTestUtils qw(create_table_test_person drop_table_test_person populate_table_test_person);
use strict;

my $dbtype = $App::options{dbtype} || "mysql";

if (!$App::options{dbuser}) {
    ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
    exit(0);
}

my $context = App->context(
    conf_file => "",
    conf => {
        Repository => {
            default => {
                class => $App::options{dbclass},
                dbdriver => $App::options{dbdriver},
                dbhost => $App::options{dbhost},
                dbname => $App::options{dbname},
                dbuser => $App::options{dbuser},
                dbpass => $App::options{dbpass},
                table => {
                    test_person => {
                        primary_key => ["person_id"],
                    },
                },
            },
        },
    },
    debug_sql => $App::options{debug_sql},
    trace => $App::options{trace},
);

my $rep = $context->repository();

&drop_table_test_person($rep);
&create_table_test_person($rep);
&populate_table_test_person($rep);

my $columns = [ "person_id", "age", "first_name", "gender", "state", "birth_dt" ];

sub check_exec {
    my ($sql, $expected_rows) = @_;

    my ($nrows);
    eval {
        $nrows = $rep->_do($sql);
    };
    is($@,"","sql ok");

    if (defined $expected_rows) {
        is($nrows, $expected_rows, "num rows $expected_rows");
    }
}

my ($sql, $expect_sql);

###############################################################################
# UPDATE
###############################################################################
$expect_sql = <<EOF;
update test_person set
   age = 6
EOF
#$sql = $rep->_mk_update_sql("test_person",{},"age",6);
#is($sql, $expect_sql, "_mk_update_sql(): 1 col, no params");
#&check_exec($sql,7);

$sql = $rep->_mk_update_sql("test_person",undef,["age"],[6]);
is($sql, $expect_sql, "_mk_update_sql(): 1 col, undef params");
&check_exec($sql,7);

$sql = $rep->_mk_update_sql("test_person",{},["age"],[6]);
is($sql, $expect_sql, "_mk_update_sql(): 1 col, no params");
&check_exec($sql,7);

$expect_sql = <<EOF;
update test_person set
   age = 6,
   state = 'GA'
where person_id = 4
EOF
$sql = $rep->_mk_update_sql("test_person",4,["age","state"],[6,"GA"]);
is($sql, $expect_sql, "_mk_update_sql(): 2 cols, by key");
&check_exec($sql,1);

$expect_sql = <<EOF;
update test_person set
   age = 6,
   state = 'GA'
where person_id = 4
EOF
$sql = $rep->_mk_update_sql("test_person",4,["age","state"],{age => 6, state => "GA"});
is($sql, $expect_sql, "_mk_update_sql(): 2 cols, by key, row is a hashref");
&check_exec($sql,1);

if ($dbtype eq 'mysql') {
    $expect_sql = <<'EOF';
update test_person set
   first_name = '%@$\\\''
where person_id = 4
EOF
}

if ($dbtype eq 'oracle') {
    $expect_sql = <<'EOF';
update test_person set
   first_name = '%@$\'''
where person_id = 4
EOF
}

$sql = $rep->_mk_update_sql("test_person",4,["first_name"],['%@$\\\'']);
is($sql, $expect_sql, "_mk_update_sql(): proper quoting for \\'");
&check_exec($sql,1);

######################################################################################3
# { by_expression => 1 }
######################################################################################3
$expect_sql = <<EOF;
update test_person set
   age = age + 1,
   state = coalesce(state,'GA')
where person_id = 4
EOF
$sql = $rep->_mk_update_sql("test_person",4,["age","state"],["age + 1", "coalesce(state,'GA')"], {by_expression => 1});
is($sql, $expect_sql, "_mk_update_sql(): by_expression");
&check_exec($sql,1);

# Doesn't work yet
#$expect_sql = <<EOF;
#update test_person set
#   age = 6
#where person_id = 4
#EOF
#$sql = $rep->_mk_update_sql("test_person",4,{age => 6});
#is($sql, $expect_sql, "_mk_update_sql(): 1 col, by key, cols/row is single hashref");
#&check_exec($sql,1);

######################################################################################3
# True updates
######################################################################################3

my ($nrows, $row);

$expect_sql = <<EOF;
update test_person set
   state = ?,
   age = ?
where gender = 'F'
EOF
$nrows = $rep->update("test_person", { gender => "F" }, ["state","age"], ["CA", 21]);
is($nrows, 3, "update(table, HASH, ARRAY, ARRAY) : nrows");
is($rep->{sql}, $expect_sql, "update(table, HASH, ARRAY, ARRAY) : sql");

$expect_sql = <<EOF;
update test_person set
   state = ?,
   age = ?
where gender = 'F'
EOF
$nrows = $rep->update("test_person", { gender => "F" }, ["state","age"], { state => "CA", age => 21 });
is($nrows, 3, "update(table, HASH, ARRAY, HASH) : nrows");
is($rep->{sql}, $expect_sql, "update(table, HASH, ARRAY, HASH) : sql");

$nrows = $rep->update("test_person", 1,
                      [ "birth_dt", "change_dttm", "modify_dttm", "age", "chess_rating", "last_name" ],
                      [ "1960-01-01", "2009-02-26 13:14:22", "2009-02-26 13:14:23", 99, 200.1, "the great" ]);
is($nrows, 1, "update(table, [DATE,DATETIME,TIMESTAMP,INT,FLOAT,STRING])");
$row = $rep->get_row("test_person", 1,
                      [ "birth_dt", "change_dttm", "modify_dttm", "age", "chess_rating", "last_name", "first_name" ]);
is($row->[0], "1960-01-01",          "update() of DATE validated");
is($row->[1], "2009-02-26 13:14:22", "update() of DATETIME validated");
is($row->[2], "2009-02-26 13:14:23", "update() of TIMESTAMP validated");
is($row->[3], 99,                    "update() of INT validated");
is($row->[4], 200.1,                 "update() of FLOAT validated");
is($row->[5], "the great",           "update() of STRING validated");

$row->[1] = "2009-02-26 13:18:01";
$row->[2] = "2009-02-26 13:19:22";
$row->[3] = 105;
$row->[4] = 213.7;

$nrows = $rep->update("test_person", [0, 5, 6],
                      [ "birth_dt", "change_dttm", "modify_dttm", "age", "chess_rating", "last_name", "first_name" ], $row);
is($nrows, 1, "update(table, ARRAY, COLARRAY, VALARRAY) updated one row");

$row = $rep->get_row("test_person", 1,
                      [ "birth_dt", "change_dttm", "modify_dttm", "age", "chess_rating", "last_name", "first_name" ]);

is($row->[1], "2009-02-26 13:18:01", "update() of DATETIME validated");
is($row->[2], "2009-02-26 13:19:22", "update() of TIMESTAMP validated");
is($row->[3], 105,                   "update() of INT validated");
is($row->[4], 213.7,                 "update() of FLOAT validated");

exit 0;

